package cn.com.jbs.course.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.com.jbs.cloudservice.conf.Config;
import cn.com.jbs.course.message.resp.Article;
import cn.com.jbs.course.service.CoreService;

/**
 * MySQL示例，通过该示例可熟悉BAE平台MySQL的使用（CRUD）
 */
public class MySQLBasic extends HttpServlet { 
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private static Logger log = LoggerFactory.getLogger(CoreService.class);
	 @Override  
	public void init() throws ServletException {  
		 		super.init();
		 log.debug("begin init db.....");
        Connection connection = null;
		Statement stmt = null;
		ResultSet rs = null;
        String sql = null;
        try {
          	/**连接数据库所需要的五要素（可从数据库详情页中查到相应信息）*****/
			String databaseName = Config.MYSQLNAME;
			String host = Config.MYSQLHOST;
			String port = Config.MYSQLPORT;
			String username = Config.USER;
			String password = Config.PWD;
			String driverName = "com.mysql.jdbc.Driver";
			String dbUrl = "jdbc:mysql://";
			String serverName = host + ":" + port + "/";
          	String connName = dbUrl + serverName + databaseName;
          
			/******接着连接并选择数据库名为databaseName的服务器******/
          	Class.forName(driverName);
			connection = DriverManager.getConnection(connName, username, password);
          	stmt = connection.createStatement();
          	/*至此连接已完全建立，就可对当前数据库进行相应的操作了*/
			/**
			 * 接下来就可以使用其它标准mysql函数操作进行数据库操作
			 */
			//创建一个数据库表
			/*sql = "create table if not exists test_mysql(" + 
						"id int primary key auto_increment," + 
						"no int, "+
						"name varchar(1024)," + 
						"key idx_no(no))";
			stmt.execute(sql);
          
          	//插入数据
			sql = "insert into test_mysql(no, name) values(2007,'this is a test message')," + 
                  		"(2008,'this is another test message'),(2009,'xxxxxxxxxxxxxx')";
			stmt.execute(sql);
          	//删除数据
			sql = "delete from test_mysql where no = 2008";
			stmt.execute(sql);
          
          	//修改数据
			sql = "update test_mysql set name = 'yyyyyy' where no = 2009";
			stmt.execute(sql);*/
          
          	//检索数据
			sql = "select ID,MSG,TITLE,DESCRIPTION,PICURL,URL from CONTENTMSG ORDER BY ID ASC ";
          	rs = stmt.executeQuery(sql);
          /*	ResultSetMetaData rsmd = rs.getMetaData();		//表的字段属性变量
			for (int i=1;i<=rsmd.getColumnCount();i++)		//按字段属性输出表的数据名
			{resp.getWriter().print(rsmd.getColumnName(i)+"\t");}*/
		
			while(rs.next())
			{
				String id=rs.getString("ID");
				
				Article article=new Article(rs.getString("TITLE"),rs.getString("DESCRIPTION"),rs.getString("PICURL"),rs.getString("URL"));
				Config.WXMSG.put(id, article);
			}
          
          	//删除表
		//	sql = "drop table if exists test_mysql";
			//stmt.execute(sql);
			
		} catch (Exception e) {
			log.error("init db error....",e);
		}finally{
			try {
				if(null !=stmt){
					stmt.close();
					connection.close();
				}
			} catch (SQLException e) {
				log.error("close conn error....",e);
			}
			
			log.debug("init db ok....");
		}
	}

}
